from logging import currentframe
import pandas as pd
import os
import requests
import random
from selenium import webdriver
import pymysql
from time import sleep
from lxml import etree
import urllib3
urllib3.disable_warnings()
import re

headers = [{"User-Agent":"Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0)"},]
header= random.choice(headers)
header['Accept'] = 'application/json,text/javascript'

class fruitWeather:
    host='192.168.0.113'
    port=3306
    user='coname'
    passwd='xZS6p4LDcKCwn4Yb'
    charset='utf8'
    db='co'
 
    def __init__(self):
        se = os.path.abspath(os.path.join(os.getcwd(),'..'),)+'\\fruit.xlsx'
        self.wb = pd.read_excel(se,sheet_name="Sheet2")
        self.wbNums = self.wb.shape[0]
        self.fruits = []
        self.url = 'http://zgnyqx.ieda.org.cn/CN/1000-6362/home.shtml'
        options = webdriver.ChromeOptions()
        options.add_argument('--ignore-certificate-errors')   #主要是该条
        options.add_argument('--ignore-ssl-errors')
        # options.add_argument('--headless')
        # options.add_argument('--disable-gpu')
        self.bro = webdriver.Chrome(options=options)
        self.bro.maximize_window()

  
    def output_excel(self):#取出数据 返回列表
        for i in range(0,self.wbNums,1):
            kv = []
            fruitName = self.wb.loc[i,'srname']
            kv.append(fruitName)
            pid = self.wb.loc[i,'keywordid']
            kv.append(pid)
            self.fruits.append(kv)
        return self.fruits

    def visit_page(self):#访问总网页
        fruits = self.output_excel()
        for fruit in fruits:
            self.bro.get(self.url)
            sleep(1)
            self.bro.current_window_handle
            #jinr
            self.visit_page_title(fruit[0],fruit[1])
            self.bro.current_window_handle
            # self.bro.close()
        self.bro.close()

    def visit_page_title(self,srname,pid):#访问子网页
        #进入到框架
        frame_path = self.bro.find_element_by_xpath('//*[@id="UserInfo"]')
        self.bro.switch_to.frame(frame_path)
        sleep(1)
        # self.bro.current_window_handle
        #获取输入框
        self.bro.find_element_by_id("val").send_keys(srname)
        #点击按钮
        sleep(1)
        cl = self.bro.find_element_by_xpath("//*[@id='form']/input[3]")
        sleep(1)
        self.bro.execute_script("arguments[0].click();",cl)
        sleep(1)
        self.bro.switch_to.default_content()
        # sleep(1)
        #页面切换为最新的
        mainWindow = self.bro.current_window_handle
        toHandle = self.bro.window_handles
        for handle in toHandle:
            if handle == mainWindow:
                continue
        self.bro.switch_to_window(handle)  
        #check选择元素
        try:
            check = self.bro.find_element_by_xpath('//*[@id="sel"]')
            sleep(1)
            self.bro.execute_script('arguments[0].click();',check)   
        except Exception as e:
            print('没有相关天气结论>>>',e)
        #找到页面的元素
        self.bro.current_window_handle
        sleep(1)
        page =  self.bro.page_source
        print('--------------------------------------------------')
        pat_url='<a href="(.*?)" class="txt_biaoti">'
        pat_tiltle='class="txt_biaoti">(.*?)</a>'
        pat_author = 'abs_zuozhe_cn">(.*?)</span>'
        pat_weather = 'class="white_content">(.*?)</div>'
        titles =  re.findall(pat_tiltle,page)
        authors = re.findall(pat_author,page)
        # weathers = re.findall(pat_weather,page)
        urls = re.findall(pat_url,page)
        num = len(titles)
        # print(urls)
        if num>0:
            for i in range(0,num,1):
                datas=[]
                datas.append(srname)
                datas.append(pid)
                srweather = self.visit_page_detail(urls[i])
                datas.append(srweather)
                srtitle=titles[i]
                datas.append(srtitle)
                srauthor = authors[i]
                datas.append(srauthor)
                self.input_mysql(datas)                
        else:
            datas=[]
            datas.append(srname)
            datas.append(pid)
            srweather=None
            datas.append(srweather)
            srtitle=None
            datas.append(srtitle)
            srauthor=None
            datas.append(srauthor)
            self.input_mysql(datas)            
        # st = etree.HTML(page)
        # arts = st.xpath('/html/body/div[3]/div[1]/div/div[2]/table/tr/td/form/div')
        # print(arts)
        # for art in arts:
        #     datas=[]
        #     url = art.xpath('.//table/tr/td[2]/a//@href')
        #     # print(url)
        #     srweather = self.visit_page_detail(url)
        #     # print(srweather)
        #     srtitle = art.xpath('.//table/tr/td[2]/a//text()')
        #     srauthor = art.xpath('.//table/tr/td[2]/div[1]/span//text()')
        #     # print(srtitle)
        #     #将字段数据放入列表
        #     
        #     #将数据存入数据库

        # datas.extend(srname,pid,srweather,srtitle,srauthor)
        # self.input_mysql(datas)
    
    def visit_page_detail(self,url):#获取详细信息
        try:
            st = etree.HTML(requests.get(url,verify=False,headers=header).text)
            content =  st.xpath('//*[@id="collapseOne"]/div/p')
            content = content[0].xpath('string(.)')
            return content
        except Exception as e:
            print("错误的原因>>>",e)
            return "标题网站打开404"
    
    def open_mysql(self):#打开数据库
        try:
            conn=  pymysql.Connection(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
            cursor = conn.cursor()
            print("打开数据库成功!")
            return conn,cursor
        except Exception as e:
            print('打开数据库失败>>>',e)

    def close_mysql(self,conn,cursor):#关闭数据库
        try:
            cursor.close()
            conn.close()
            print('关闭数据成功!')
        except Exception as e:
            print("关闭数据库失败>>>",e)

    def input_mysql(self,datas):#放入数据库
        conn,cursor = self.open_mysql()
        try:
            if not cursor.execute('select srtitle from co_fruit_weather where srtitle="{}"'.format(datas[3])):
                cursor.execute('insert into co_fruit_weather (srname,pid,srweather,srtitle,srauthor) values("{}","{}","{}","{}","{}")'.format(datas[0],datas[1],datas[2],datas[3],datas[4]))
                conn.commit()
                print('插入数据成功>>>',datas[0])
        except Exception as e:
            print('插入数据失败>>>',e)
        self.close_mysql(conn,cursor)

    def run(self):
        self.visit_page()

if __name__ == "__main__":
    fruitWeather().run()
